<?php
/**
 * Created by PhpStorm.
 * User: zhengkunming
 * Date: 2018/12/29
 * Time: 2:10 PM
 */

require_once 'mysql_config.php';

$query = 'SELECT
	shop,
	SUM( score ) shop_score 
FROM
	(
	SELECT
		shop,
		score 
	FROM
		users 
	WHERE
		1 = 1 UNION ALL
	SELECT
		shop,
		score 
	FROM
		check_in 
	WHERE
		1 = 1 
		AND created >= ( SELECT DATE_ADD( CURDATE( ), INTERVAL - DAY ( CURDATE( ) ) + 1 DAY ) ) 
		AND created <= ( SELECT LAST_DAY( CURDATE( ) ) ) UNION ALL SELECT shop, score FROM answer_score WHERE 1 = 1 AND created >= ( SELECT DATE_ADD( CURDATE( ), INTERVAL - DAY ( CURDATE( ) ) + 1 DAY ) ) 
		AND created <= ( SELECT LAST_DAY( CURDATE( ) ) ) 
	) T_shop_score 
WHERE
	1 = 1 
GROUP BY
	T_shop_score.shop 
ORDER BY
	shop_score DESC;';

$query_res = mysqli_query($link, $query);

if (FALSE === $query_res) {
    $data = array(
        'code' => -3,
        'data' => array(),
        'msg' => 'database err'
    );
    rtJson($data);
    return;
}

$query_num_rows = $query_res->num_rows;

if (0 === $query_num_rows) {
    $data = array(
        'code' => -2,
        'data' => array(),
        'msg' => 'not shop rankings'
    );
    rtJson($data);
    return;
} else {
    $list = $query_res->fetch_all(MYSQLI_ASSOC);
    $data = array(
        'code' => 0,
        'data' => $list,
        'msg' => 'shop rankings'
    );
    rtJson($data);
}


